﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetUserMenuImagesForUser')
	BEGIN
		DROP  Procedure  [esf_sso].P_GetUserMenuImagesForUser
	END

GO

-- [esf_sso].P_GetUserMenuImagesForUser NULL, 'ESF.SSO.Client'
-- [esf_sso].P_GetUserMenuImagesForUser 1, 'ESF.SSO.Client'


CREATE Procedure [esf_sso].P_GetUserMenuImagesForUser
	@pPersonID			BIGINT = NULL
	,@pApplicationCode	VARCHAR (60)
AS

	-- Usuario Publico
	IF @pPersonID IS NULL
	BEGIN
	
		SELECT
			F.ID			File_ID
			,F.URL			File_URL
			,F.Description	File_Description
			,VFC.ID			File_FilCat_ID
			,VFC.Code		File_FilCat_Code
			,VFC.Name		File_FilCat_Name
			,M.ID			File_Menu_ID
			,M.Code			File_Menu_Code
		FROM
			esf_files.[File] F
		LEFT JOIN
			esf_sso.VFileCategory VFC ON VFC.ID = F.FileTypeID
		INNER JOIN
			esf_sso.TreeBasicReference TBR ON 
				TBR.BasicReferenceID = F.ID
		INNER JOIN
			(SELECT
				DISTINCT VU.ChiBR_ID	ID
				,VU.ChiBR_Code			Code
			FROM 
				[esf_sso].VUserMenus VU
			-- UserMenu , UserGroup (parent)
			INNER JOIN [esf_sso].TreeBasicReference UG_UM ON
				UG_UM.BasicReferenceID = VU.ChiBR_ID
			INNER JOIN
				[esf_sso].VApplication VA ON
					VA.ID = VU.ParBR_ID
			WHERE 
				UG_UM.ParentBasicReferenceID = (SELECT ID FROM [esf_sso].VUserGroup WHERE Code = 'Public')
				AND VA.Code = @pApplicationCode
				AND VU.ChiBR_IsEnabled = 1) M
			ON M.ID = TBR.ParentBasicReferenceID
	
	END
	ELSE
	BEGIN
	
		SELECT
			F.ID			File_ID
			,F.URL			File_URL
			,F.Description	File_Description
			,VFC.ID			File_FilCat_ID
			,VFC.Code		File_FilCat_Code
			,VFC.Name		File_FilCat_Name
			,M.ID			File_Menu_ID
			,M.Code			File_Menu_Code
		FROM
			esf_files.[File] F
		LEFT JOIN
			esf_sso.VFileCategory VFC ON VFC.ID = F.FileTypeID
		INNER JOIN
			esf_sso.TreeBasicReference TBR ON 
				TBR.BasicReferenceID = F.ID
		INNER JOIN
			(SELECT
				DISTINCT VU.ChiBR_ID	ID
				,VU.ChiBR_Code			Code
			FROM 
				[esf_sso].VUserMenus VU
			-- UserMenu , UserGroup (parent)
			INNER JOIN [esf_sso].TreeBasicReference UG_UM ON
				UG_UM.BasicReferenceID = VU.ChiBR_ID
			INNER JOIN [esf_sso].[User_BasicReference] UUG ON
				UUG.BasicReferenceID = UG_UM.ParentBasicReferenceID
			INNER JOIN
				[esf_sso].VApplication VA ON
					VA.ID = VU.ParBR_ID
			WHERE 
				UUG.UserPersonID = @pPersonID
				AND VA.Code = @pApplicationCode
				AND VU.ChiBR_IsEnabled = 1) M
			ON M.ID = TBR.ParentBasicReferenceID
			
	END
	
GO  